package com.whcoding.test.common.excel;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import com.whcoding.test.constant.BaseConstant;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.util.*;

/**
 * @program: spring-boot-learning
 * @description:
 * @author: whcoding
 * @create: 2022-02-15 14:00
 **/
public class ExcelUtils {

	private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class);

	public static List importExcel(String fileName, Class clazz, Integer titleRows, Integer headRows) {
		if (titleRows == null || titleRows > 1 || titleRows < 0) {
			titleRows = 0;
		}
		if (headRows == null || headRows > 2 || headRows < 1) {
			headRows = 1;
		}
		List list = null;
		ImportParams params = new ImportParams();
		params.setTitleRows(titleRows);
		params.setHeadRows(headRows);
		try {
			long start = System.currentTimeMillis();
			list = ExcelImportUtil.importExcel(new File(fileName), clazz, params);
			long end = System.currentTimeMillis();
			log.info("导入数据耗时：{}", (end - start));
		} catch (Exception e) {
			log.error("导入数据出现异常！", e);
		}
		return list;
	}

	public static List importExcel(String fileName, Class clazz, Integer titleRows, Integer headRows, Integer startSheetIndex) {
		if (titleRows == null || titleRows > 1 || titleRows < 0) {
			titleRows = 0;
		}
		if (headRows == null || headRows > 2 || headRows < 1) {
			headRows = 1;
		}
		if (startSheetIndex == null || startSheetIndex < 0) {
			startSheetIndex = 0;
		}
		List list = null;
		ImportParams params = new ImportParams();
		params.setTitleRows(titleRows);
		params.setHeadRows(headRows);
		params.setStartSheetIndex(startSheetIndex);
		try {
			long start = System.currentTimeMillis();
			list = ExcelImportUtil.importExcel(new File(fileName), clazz, params);
			long end = System.currentTimeMillis();
			log.info("导入数据耗时：{}", (end - start));
		} catch (Exception e) {
			log.error("导入数据出现异常！", e);
		}
		return list;
	}


	/**
	 * 导出excel 方法重载
	 *
	 * @param fileName
	 * @param pojoClass
	 * @param dataSet
	 * @return
	 */
	public static String exportExcel(String fileName, Class<?> pojoClass, Collection<?> dataSet) {
		return exportExcel(fileName, pojoClass, dataSet, false);
	}

	/**
	 * @param pojoClass 导出对象的类型
	 * @param dataSet   导出的集合
	 * @Description:用于一般导出,默认没有标题名称和sheet名称
	 * @param:fileName 导出excel的名称
	 */
	public static String exportExcel(String fileName, Class<?> pojoClass, Collection<?> dataSet, Boolean isSave) {
		String fileId = "";
		try {
			long start = System.currentTimeMillis();
			String excelName = fileName + "-" + DateUtil.format(new Date(), DatePattern.PURE_DATETIME_PATTERN);
			Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(null, fileName), pojoClass, dataSet);

			File file = new File(getDesPath(excelName));
			BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(file));
			workbook.write(bos);

			if (bos != null) {
				bos.close();
			}
			//删除本地文件
			if (isSave) {
				delFile(file);
				log.info("本地文件：{} 删除成功", excelName);
			}
			log.info("导出数据和上传耗时：{},文件名称：{}", (System.currentTimeMillis() - start), excelName);
		} catch (Exception e) {
			log.error("导出数据出现异常！", e.getMessage());
		}
		return fileId;
	}

	/**
	 * 获取文件路径
	 *
	 * @param excelName
	 * @return
	 */
	private static String getDesPath(String excelName) {

		String path = System.getProperty("user.dir");

		if (path.endsWith("/")) {
			path = path + excelName + ".xls";
		} else {
			path = path + File.separatorChar + excelName + ".xls";
		}
		return path;
	}

	/**
	 * 删除文件
	 *
	 * @param file
	 * @return
	 */
	private static boolean delFile(File file) {
		if (file.exists() && file.isFile()) {
			return file.delete();
		}
		return false;
	}

	/**
	 *
	 * @param filePath 文件地址
	 * @return
	 * @throws IOException
	 */
	public static Map<String, String> excelConvertToMap(String filePath) throws IOException {
		Workbook workbook = null;
		try {
			//解析2003
			workbook = new XSSFWorkbook(new FileInputStream(filePath));
		} catch (Exception e) {
			//解析2007
			workbook = new HSSFWorkbook(new FileInputStream(filePath));
		}
		return readExcelConverMap(workbook);
	}
	/**
	 * 将excel 转为map
	 *
	 * @param filePath 文件地址
	 * @param suffixStr 文件后缀
	 * @return
	 */
	public static Map<String, String> excelConvertToMap(String filePath, String suffixStr) {
		// XSSFWorkbook 2007
		// HSSFWorkbook 2003
		try (FileInputStream is = new FileInputStream(filePath)) {
			Workbook hssfWorkbook = null;
			if (".xls".equals(suffixStr)) {
				hssfWorkbook = new HSSFWorkbook(is);
			} else {
				hssfWorkbook = new XSSFWorkbook(is);
			}
			return readExcelConverMap(hssfWorkbook);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 解析excel 为前2列的数据
	 *
	 * @param hssfWorkbook
	 * @return
	 * @throws Exception
	 */
	public static Map<String, String> readExcelConverMap(Workbook hssfWorkbook) {

		Map<String, String> hashMap = new HashMap<>(16);
		FormulaEvaluator evaluator = hssfWorkbook.getCreationHelper().createFormulaEvaluator();
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			//获取首行
//			Row hssfRowFirst = hssfSheet.getRow(0);
//			for (int rowNum = 0; rowNum < hssfRowFirst.getLastCellNum(); rowNum++){
//				Cell key=hssfRowFirst.getCell(rowNum);
//				System.out.println(key);
//			}
			// 循环行Row
			for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				Row hssfRow = hssfSheet.getRow(rowNum);

				if (hssfRow != null) {
					Cell key = hssfRow.getCell(0);
					Cell cell = hssfRow.getCell(1);
					//解析文件中各种类型的数据信息
					String stringCellValue = getStringCellValue(cell, evaluator);
					//key值为去空格的值
					hashMap.put(key.toString().trim(), stringCellValue);
				}
			}
		}
		return hashMap;
	}


	/**
	 * @param cell Excel单元格
	 * @return String 单元格数据内容
	 * <p>
	 * 使用Hutool工具包的ExcelReader.read()方法报错，
	 * 因为poi3.17版本的Cell.getCellType()返回int
	 * 而不是org.apache.poi.ss.usermodel.CellType。
	 * CellType
	 * 加入Poi3.9 依赖即可。
	 */
	private static String getStringCellValue(Cell cell, FormulaEvaluator evaluator) {
		//若表格为空，则直接返回空值
		if (cell == null) {
			return BaseConstant.DEFAULT_STRING_VALUE;
		}
		String strCell = "";
		switch (cell.getCellTypeEnum()) {
			//    表格中的数据为字符串型
			case STRING:
				strCell = cell.getStringCellValue();
				break;
			//    表格中的数据为数值型
			case NUMERIC:
				strCell = String.valueOf(cell.getNumericCellValue());
				break;
			//    表格中的数据为布尔型
			case BOOLEAN:
				strCell = String.valueOf(cell.getBooleanCellValue());
				break;
			//    表格中的数据为空值
			case BLANK:
				strCell = "";
				break;
			//    表格中的数据信息为公式类
			case FORMULA:
				double numberValue = evaluator.evaluate(cell).getNumberValue();
				strCell = String.valueOf(numberValue);
				break;
			default:
				strCell = "";
				break;
		}
		if (strCell.equals("") || strCell == null) {
			return "";
		}
		return strCell;
	}

}
